#
import logging
import os
import time
import re
import openpyxl
import threading

from PyQt5.QtCore import QThread, pyqtSignal

# 全局变量用于存储模板工作簿前三行数据
template_header_rows = []


def validate_string(input_string):
    # 判断字符串是否为空
    if not input_string:
        return ''
    # 判断字符串长度是否足够，这里假设长度需大于等于 20
    if len(input_string) < 20:
        return ''
    # 判断字符串是否含有字母或特殊字符
    if re.search(r'[a-zA-Z\W]', input_string):
        return ''
    return input_string


def read_txt_file(file_path):
    try:
        with open(file_path, 'r') as f:
            return f.readlines()
    except FileNotFoundError:
        logging.error(f"File not found: {file_path}")
        return None


class OP70ConvertWorker(QThread):
    statusMessage = pyqtSignal(str)

    def __init__(self, files_path):
        super().__init__()
        self.Error_line_ = 0
        self.reportType = None
        self.file_names = []
        self.files_path = files_path
        self.new_excel_path = os.path.join(self.files_path, f'NewOP70{time.strftime("%Y%m%d%H%M%S")}.xlsx')
        # self.template_path = template_path
        try:
            self.wb = openpyxl.Workbook()
            self.ws = self.wb.active
            self.ws.title = "Data"
            # self.template_path = template_path
        except Exception as e:
            self.statusMessage.emit(f"Error creating new Excel: {e}")

    # data_row = [date, time, part_number, Tp3_Errors, Tp3_TestTime, Tp3_Status, Tp3_ErrFlags1, Tp4_Status, TP4_ErrFlags2,
    #             Tp4_Status2, Tp5_umot, Tp6_is1, Tp6_is2, Tp6_uo, Tp6_iv, Tp7_is1, Tp7_is2, Tp7_uo, Tp8_umot, Tp9_is1,
    #             Tp9_is2, Tp9_uo, Tp17_is1, Tp17_is2, Tp21_is1, Tp21_is2]
    # all_data.append(data_row)
    def title_for_Lxe(self):  # 设置标题，Excel中第一行数据
        try:
            column_names = ['Date', 'Time', 'SNo', 'TP2 (sensor read)_TestTime [s]', 'TP3_Status', 'Tp5_IS',
                            'Tp5_IS_Status', 'Tp5_Uo[V]', 'Tp5_Uo_Status', 'Tp5_Iv[mA]', 'Tp5_Iv_Status'
                , 'Tp6_IS', 'Tp6_IS_Status', 'Tp6_Uo[V]', 'Tp6_Uo_Status', 'Tp6_Iv[mA]', 'Tp6_Iv_Status', 'Tp8_IS',
                            'Tp8_IS_Status', 'Tp8_Uo[V]', 'Tp8_Uo_Status', 'Tp8_Iv[mA]', 'Tp8_Iv_Status',
                            'Tp11_IS', 'Tp11_IS_Status', 'Tp11_Uo[V]', 'Tp11_Uo_Status', 'Tp11_Iv[mA]', 'Tp11_Iv_Status'

                            ]
            for col_idx, col_name in enumerate(column_names, start=1):
                self.ws.cell(row=1, column=col_idx, value=col_name)

        except Exception as e:
            # logging.error(f"Error setting title: {e}")
            self.statusMessage.emit(f"Error setting title: {e}")

    def title_(self):  # 设置标题，Excel中第一行数据
        try:
            column_names = ['Date', 'Time', 'SNo', 'Tp3_Errors', 'Tp3_TestTime', 'Tp3_Status', 'Tp3_ErrFlags1',
                            'Tp4_Status', 'TP4_ErrFlags2'
                , 'Tp4_Status2', 'TP5_Umot[V]', 'TP6_IS1[%]', 'TP6_IS2[%]', 'TP6_Uo[V]', 'TP6_Iv[mA]'
                , 'TP7_IS1[%]', 'TP7_IS2[%]', 'TP7_Uo[V]', 'TP8_Umot[V]', 'TP9_IS1[%]', 'TP9_IS2[%]', 'TP9_Uo[V]',
                            'TP17_IS1[%]', 'TP17_IS2[%]', 'TP21_IS1[%]', 'TP21_IS2[%]'
                            ]
            for col_idx, col_name in enumerate(column_names, start=1):
                self.ws.cell(row=1, column=col_idx, value=col_name)

        except Exception as e:
            # logging.error(f"Error setting title: {e}")
            self.statusMessage.emit(f"Error setting title: {e}")
        # 写入标题到第一行，内存中 此时还没有写如excel

    # def read_Op70_data(self, file_path):
    #     all_data = []
    #     lines = read_txt_file(file_path)
    #     try:
    #         self.statusMessage.emit(f"Reading file: {file_path}")
    #         if lines is not None:
    #             for i in range(3, len(lines)):
    #                 if lines[i]:
    #                     if validate_string(lines[i].split()[4]):  # 先判断读取出来的partnumber 是正常的数据
    #                         part_number = validate_string(lines[i].split()[4])
    #                     else:
    #                         continue
    #                     date = lines[i].split()[0]
    #                     time = lines[i].split()[1] + '' + lines[i].split()[2]
    #                     if 'NOK' in lines[i].split():
    #                         data_row = [date, time, part_number]
    #                         for y in range(10, len(lines[i].split()) - 29):
    #                             data_row.append(lines[i].split()[y])
    #                         all_data.append(data_row)
    #                         continue
    #                     else:
    #                         Tp5_umot = lines[i].split()[16]
    #                         Tp6_is1 = lines[i].split()[18]
    #                         Tp6_is2 = lines[i].split()[20]
    #                         Tp6_uo = lines[i].split()[22]
    #                         Tp6_iv = lines[i].split()[24]
    #                         Tp7_is1 = lines[i].split()[26]
    #                         Tp7_is2 = lines[i].split()[28]
    #                         Tp7_uo = lines[i].split()[30]
    #                         Tp8_umot = lines[i].split()[36]
    #                         Tp9_is1 = lines[i].split()[38]
    #                         Tp9_is2 = lines[i].split()[40]
    #                         Tp9_uo = lines[i].split()[42]
    #                         Tp17_is1 = lines[i].split()[78]
    #                         Tp17_is2 = lines[i].split()[80]
    #                         Tp21_is1 = lines[i].split()[95]
    #                         Tp21_is2 = lines[i].split()[97]
    #                         data_row = [date, time, part_number, Tp5_umot, Tp6_is1, Tp6_is2,
    #                                     Tp6_uo, Tp6_iv, Tp7_is1, Tp7_is2, Tp7_uo, Tp8_umot, Tp9_is1,
    #                                     Tp9_is2, Tp9_uo, Tp17_is1, Tp17_is2, Tp21_is1, Tp21_is2]
    #                     all_data.append(data_row)
    #                 else:
    #                     pass
    #     except Exception as e:
    #         self.statusMessage.emit(f"Error reading file: {e}")
    #     return all_data

    # 判断一下是哪个报告类型 rek /lex 默认是rek
    def judge_reportType(self, file_path):
        files = self.get_file_names(file_path)
        file_path = os.path.join(self.files_path, files[0])  # 获取文件路径相对的路径
        # print('获取第一个文件的内容：判断是什么型号的报告', file_path)
        lines = read_txt_file(file_path)
        if lines[0].split()[2] == 'MEP0311':
            self.reportType = 'REK'
        elif lines[0].split()[2] == 'MEP0283A':
            self.reportType = 'LXE'
        else:
            self.reportType = 'REK'

    # 主要的数据处理过程
    def read_Op70_data(self, file_path):
        # logging.info('reportType:{}'.format(self.reportType))
        all_data = []
        data_row = []
        lines = read_txt_file(file_path)  # 读取文件
        try:
            self.statusMessage.emit(f"Reading file: {file_path}")
            if lines is not None:
                for i in range(3, len(lines)):
                    if lines[i]:
                        if validate_string(lines[i].split()[4]):  # 先判断读取出来的partnumber 是正常的数据
                            part_number = validate_string(lines[i].split()[4])
                        else:
                            continue
                        date = lines[i].split()[0]
                        time = lines[i].split()[1] + '' + lines[i].split()[2]
                        Tp3_Status = lines[i].split()[9]
                        Tp3_Errors = lines[i].split()[5]
                        Tp3_TestTime = lines[i].split()[8]
                        if self.reportType == 'REK':
                            if 'NOK' in lines[i].split():
                                Tp3_Status = 'NOK'
                                data_row = [date, time, part_number, Tp3_Errors, Tp3_TestTime, Tp3_Status]
                            elif 'OK' not in lines[i].split()[9] and 'NOK' not in lines[i].split()[9]:
                                Tp3_Status = 'NOK_invalidData'
                                data_row = [date, time, part_number, Tp3_Errors, Tp3_TestTime, Tp3_Status]
                            elif Tp3_Errors != '$000000':
                                Tp3_Status = 'invalidData'
                                data_row = [date, time, part_number, Tp3_Errors, Tp3_TestTime, Tp3_Status]
                            else:
                                Tp3_ErrFlags1 = lines[i].split()[10]
                                Tp4_Status = lines[i].split()[11]
                                TP4_ErrFlags2 = lines[i].split()[12]
                                Tp4_Status2 = lines[i].split()[13]
                                Tp5_umot = lines[i].split()[16]
                                Tp6_is1 = lines[i].split()[18]
                                Tp6_is2 = lines[i].split()[20]
                                Tp6_uo = lines[i].split()[22]
                                Tp6_iv = lines[i].split()[24]
                                Tp7_is1 = lines[i].split()[26]
                                Tp7_is2 = lines[i].split()[28]
                                Tp7_uo = lines[i].split()[30]
                                Tp8_umot = lines[i].split()[36]
                                Tp9_is1 = lines[i].split()[38]
                                Tp9_is2 = lines[i].split()[40]
                                Tp9_uo = lines[i].split()[42]
                                Tp17_is1 = lines[i].split()[78]
                                Tp17_is2 = lines[i].split()[80]
                                Tp21_is1 = lines[i].split()[95]
                                Tp21_is2 = lines[i].split()[97]
                                data_row = [date, time, part_number, Tp3_Errors, Tp3_TestTime, Tp3_Status,
                                            Tp3_ErrFlags1,
                                            Tp4_Status, TP4_ErrFlags2, Tp4_Status2, Tp5_umot, Tp6_is1, Tp6_is2, Tp6_uo,
                                            Tp6_iv, Tp7_is1, Tp7_is2, Tp7_uo, Tp8_umot, Tp9_is1, Tp9_is2, Tp9_uo,
                                            Tp17_is1,
                                            Tp17_is2, Tp21_is1, Tp21_is2]
                        elif self.reportType == 'LXE':
                            TP2_TestTime = lines[i].split()[8]
                            TP3_Status = lines[i].split()[9]

                            if TP2_TestTime < '1.0':
                                self.Error_line_ += 1
                                if self.Error_line_ == 1:
                                    Tp8_Uo = lines[i].split()[9]
                                    Tp8_Uo_Status = lines[i].split()[10]
                                    Tp8_Iv = lines[i].split()[11]
                                    Tp8_Iv_Status = lines[i].split()[12]
                                    Tp11_IS = lines[i].split()[13]
                                    Tp11_IS_Status = lines[i].split()[14]
                                    data_row = [date, time, part_number, TP2_TestTime, '', '', '', '', '', '', '', '',
                                                '', '', '', '', '', '', '', '', '', '', '', Tp8_Uo, Tp8_Uo_Status,
                                                Tp8_Iv, Tp8_Iv_Status, Tp11_IS, Tp11_IS_Status]
                                elif self.Error_line_ == 2:
                                    Tp8_Uo = lines[i].split()[9]
                                    Tp8_Uo_Status = lines[i].split()[10]
                                    Tp8_Iv = lines[i].split()[11]
                                    Tp8_Iv_Status = lines[i].split()[12]
                                    Tp11_IS = lines[i].split()[13]
                                    Tp11_IS_Status = lines[i].split()[14]
                                    data_row = [date, time, part_number, TP2_TestTime, '', '', '', '', '', '',
                                                '', '', '', '', '', '', '', '', '', Tp8_Uo, Tp8_Uo_Status, Tp8_Iv,
                                                Tp8_Iv_Status, Tp11_IS, Tp11_IS_Status]
                                    self.Error_line_ = 0
                            else:
                                Tp5_IS = lines[i].split()[10]
                                Tp5_IS_Status = lines[i].split()[11]
                                Tp5_Uo = lines[i].split()[12]
                                Tp5_Uo_Status = lines[i].split()[13]
                                Tp5_Iv = lines[i].split()[14]
                                Tp5_Iv_Status = lines[i].split()[15]

                                Tp6_IS = lines[i].split()[16]
                                Tp6_IS_Status = lines[i].split()[17]
                                Tp6_Uo = lines[i].split()[18]
                                Tp6_Uo_Status = lines[i].split()[19]
                                Tp6_Iv = lines[i].split()[20]
                                Tp6_Iv_Status = lines[i].split()[21]

                                Tp8_IS = lines[i].split()[23]
                                Tp8_IS_Status = lines[i].split()[24]
                                Tp8_Uo = lines[i].split()[25]
                                Tp8_Uo_Status = lines[i].split()[26]
                                Tp8_Iv = lines[i].split()[27]
                                Tp8_Iv_Status = lines[i].split()[28]
                                if len(lines[i].split()[29]) > 3:
                                    Tp11_IS = ''
                                else:
                                    Tp11_IS = lines[i].split()[29]
                                Tp11_IS_Status = ''
                                Tp11_Uo = lines[i].split()[31]
                                Tp11_Uo_Status = lines[i].split()[32]
                                Tp11_Iv = lines[i].split()[33]
                                Tp11_Iv_Status = lines[i].split()[34]

                                data_row = [date, time, part_number, TP2_TestTime, TP3_Status,
                                            Tp5_IS, Tp5_IS_Status, Tp5_Uo, Tp5_Uo_Status, Tp5_Iv, Tp5_Iv_Status,
                                            Tp6_IS, Tp6_IS_Status, Tp6_Uo, Tp6_Uo_Status, Tp6_Iv, Tp6_Iv_Status,
                                            Tp8_IS, Tp8_IS_Status, Tp8_Uo, Tp8_Uo_Status, Tp8_Iv, Tp8_Iv_Status

                                            ]
                        all_data.append(data_row)
        except Exception as e:
            self.statusMessage.emit(f"Error reading file: {e}")
        return all_data

    def get_file_names(self, directory):
        file_list = [f for f in os.listdir(directory) if f.endswith('.txt')]
        return file_list

    def run(self):
        try:
            print('reading OP70 data')
            self.judge_reportType(self.files_path)
            if self.reportType == 'LXE':
                self.title_for_Lxe()
            elif self.reportType == 'REK':
                self.title_()

            self.file_names = self.get_file_names(self.files_path)  # 获取文件夹下的所有文件名

            threads = []
            for file_name in self.file_names:
                # print(f'Processing file: {file_name}')
                self.statusMessage.emit(f"Processing file: {file_name}")
                file_path = os.path.join(self.files_path, file_name)  # 获取文件路径相对的路径

                data = self.read_Op70_data(file_path)
                for row_data in data:
                    self.ws.append(row_data)
            self.wb.save(self.new_excel_path)
            self.statusMessage.emit('Conversion completed!')

            self.statusMessage.emit(f'Would save here: {self.new_excel_path}')
            self.Error_line_ = 0


        except Exception as e:
            self.statusMessage.emit(f'Conversion failed! due to {str(e)}')
            # logging.error(f"An error occurred during the conversion process: {e}")
            self.Error_line_ = 0
            return False


if __name__ == '__main__':
    template_path = r'D:\M4REK\test\OP70 calibration\OP70Template.xlsx'
